drop table cases  cascade constraints;
drop table joueurs cascade constraints;
drop table categories cascade constraints;
drop table Questions cascade constraints;
drop table reponses cascade constraints;
drop table position cascade constraints;
drop table score cascade constraints;


CREATE TABLE Cases 
    ( 
     CoordX NUMBER  NOT NULL , 
     CoordY NUMBER  NOT NULL , 
     Type CHAR (1) , 
     CodeCategorie CHAR (1)  NOT NULL 
    ) 
;



ALTER TABLE Cases 
    ADD CONSTRAINT Case_Pkv2 PRIMARY KEY ( CoordX, CoordY ) ;


CREATE TABLE Categories 
    ( 
     CodeCategorie CHAR (1)  NOT NULL , 
     NomCategorie VARCHAR2 (30) , 
     Couleur VARCHAR2 (20) 
    ) 
;



ALTER TABLE Categories 
    ADD CONSTRAINT Categorie_PK PRIMARY KEY ( CodeCategorie ) ;


CREATE TABLE Joueurs 
    ( 
     Alias VARCHAR2 (20)  NOT NULL , 
     Nom VARCHAR2 (20) , 
     Prenom VARCHAR2 (20) 
    ) 
;



ALTER TABLE Joueurs 
    ADD CONSTRAINT Joueurs_PK PRIMARY KEY ( Alias ) ;


CREATE TABLE Position 
    ( 
     NumTour NUMBER  NOT NULL , 
     Alias VARCHAR2 (20)  NOT NULL , 
     CoordX NUMBER  NOT NULL , 
     CoordY NUMBER  NOT NULL 
    ) 
;



ALTER TABLE Position 
    ADD CONSTRAINT Position_PK PRIMARY KEY ( NumTour, Alias ) ;


CREATE TABLE Questions 
    ( 
     NumQuestion CHAR (6)  NOT NULL , 
     EnonceQuestion VARCHAR2 (40) , 
     Flag CHAR (1) , 
     CodeCategorie CHAR (1)  NOT NULL 
    ) 
;



ALTER TABLE Questions 
    ADD CONSTRAINT Questions_PK PRIMARY KEY ( NumQuestion ) ;


CREATE TABLE Reponses 
    ( 
     NumReponse CHAR (6)  NOT NULL , 
     Description VARCHAR2 (40) , 
     EstBonne CHAR , 
     NumQuestion CHAR (6)  NOT NULL 
    ) 
;



ALTER TABLE Reponses 
    ADD CONSTRAINT Reponse_PK PRIMARY KEY ( NumReponse ) ;


CREATE TABLE Score 
    ( 
     CodeCategorie CHAR (1)  NOT NULL , 
     Alias VARCHAR2 (20)  NOT NULL 
    ) 
;



ALTER TABLE Score 
    ADD CONSTRAINT PK_Score PRIMARY KEY ( CodeCategorie, Alias ) ;



ALTER TABLE Questions 
    ADD CONSTRAINT Relation_2 FOREIGN KEY 
    ( 
     CodeCategorie
    ) 
    REFERENCES Categories 
    ( 
     CodeCategorie
    ) 
    ON DELETE CASCADE 
;


ALTER TABLE Reponses 
    ADD CONSTRAINT Relation_3 FOREIGN KEY 
    ( 
     NumQuestion
    ) 
    REFERENCES Questions 
    ( 
     NumQuestion
    ) 
    ON DELETE CASCADE 
;


ALTER TABLE Cases 
    ADD CONSTRAINT Relation_5 FOREIGN KEY 
    ( 
     CodeCategorie
    ) 
    REFERENCES Categories 
    ( 
     CodeCategorie
    ) 
    ON DELETE CASCADE 
;


ALTER TABLE Position 
    ADD CONSTRAINT Relation_8 FOREIGN KEY 
    ( 
     Alias
    ) 
    REFERENCES Joueurs 
    ( 
     Alias
    ) 
;


ALTER TABLE Position 
    ADD CONSTRAINT Relation_9 FOREIGN KEY 
    ( 
     CoordX,
     CoordY
    ) 
    REFERENCES Cases 
    ( 
     CoordX,
     CoordY
    ) 
;


ALTER TABLE Score 
    ADD CONSTRAINT Score_Categories_FK FOREIGN KEY 
    ( 
     CodeCategorie
    ) 
    REFERENCES Categories 
    ( 
     CodeCategorie
    ) 
;


ALTER TABLE Score 
    ADD CONSTRAINT Score_Joueurs_FK FOREIGN KEY 
    ( 
     Alias
    ) 
    REFERENCES Joueurs 
    ( 
     Alias
    ) 
;

-------------------INSERTION DANS LA TABLE CATEGORIES---------------------
-------------------LES NOMS DE CATÉGORIES PEUVENT CHANGER SELON LES VOTRES
-------------------MAIS PAS LE CODECATEGORIE.

insert into categories values ('V','Sciences', 'Vert');
insert into categories values ('B','Histoire', 'Bleu');
insert into categories values ('J','Sport', 'Jaune');
insert into categories values ('R','Culture et Art', 'Rouge'); 
INSERT INTO CATEGORIES VALUES ('N','NULLE','BLANC');
COMMIT;
-----------------------------------------------

----------------INSERTION LIGNE 1 -------------
insert into cases values  (0, 0, 'R','J');
insert into cases values  (0, 1, 'A','B');
insert into cases values  (0, 2, 'A','J');
insert into cases values  (0, 3, 'A','V');
insert into cases values  (0, 4, 'A','R');

insert into cases values  (0, 5, 'G','B');  
-------
insert into cases values  (0, 6, 'A','J');
insert into cases values  (0, 7, 'A','V');
insert into cases values  (0, 8, 'A','R');
insert into cases values  (0, 9, 'A','B');
insert into cases values  (0, 10, 'R','V');

------------------INSERTION LIGNE 2--------
--DELETE FROM CASES  WHERE COORDX =1 ;
INSERT INTO CASES VALUES (1,0,'A','R');
INSERT INTO CASES VALUES (1,5,'A','V');
INSERT INTO CASES VALUES (1,10,'A','J');

-----------INSERTION LIGNE 3 ---------
INSERT INTO  CASES VALUES (2, 0, 'A','V');
INSERT INTO  CASES VALUES (2, 5, 'A','R');
INSERT INTO  CASES VALUES (2, 10, 'A','V');

------------------INSERTION LIGNE 4
INSERT INTO  CASES VALUES (3, 0, 'A','J');
INSERT INTO  CASES VALUES (3, 5, 'A','B');
INSERT INTO  CASES VALUES (3, 10, 'A','R');

-----------INSERTION LIGNE 5
INSERT INTO  CASES VALUES (4, 0, 'A','B');
INSERT INTO  CASES VALUES (4, 5, 'A','J');
INSERT INTO  CASES VALUES (4, 10, 'A','B');

---------------INSERTION LIGNE 6
INSERT INTO CASES VALUES (5,0,'G','R');
INSERT INTO CASES VALUES (5,1, 'A','J');
INSERT INTO CASES VALUES (5,2,'A','V');
INSERT INTO CASES VALUES (5,3,'A','R');
INSERT INTO CASES VALUES (5,4,'A','B');
------
INSERT INTO CASES VALUES (5,5,'D','N');
----
INSERT INTO CASES VALUES (5,6,'A','V');
INSERT INTO CASES VALUES (5,7,'A','J');
INSERT INTO CASES VALUES (5,8,'A','B');
INSERT INTO CASES VALUES (5,9,'A','R');
INSERT INTO CASES VALUES (5,10,'G','J');

-------------------INSERTION LIGNE 7---------
INSERT INTO CASES VALUES (6,0,'A','V');
INSERT INTO CASES VALUES (6,5,'A','R');
INSERT INTO CASES VALUES (6,10,'A','V');

------INSERTION LIGNE 8----
INSERT INTO CASES VALUES (7,0,'A','J');
INSERT INTO CASES VALUES (7,5,'A','V');
INSERT INTO CASES VALUES (7,10,'A','R');

----INSERTION LIGNE 9---
INSERT INTO CASES VALUES (8,0,'A','B');
INSERT INTO CASES VALUES (8,5,'A','J');
INSERT INTO CASES VALUES (8,10,'A','B');

--------INSERTION LIGNE 10
INSERT INTO CASES VALUES (9,0,'A','R');
INSERT INTO CASES VALUES (9,5,'A','B');
INSERT INTO CASES VALUES (9,10,'A','J');

-----------INSERTION LIGNE 11----
INSERT INTO CASES VALUES (10,0,'R','B');
INSERT INTO CASES VALUES (10,1,'A','V');
INSERT INTO CASES VALUES (10,2,'A','J');
INSERT INTO CASES VALUES (10,3,'A','B');
INSERT INTO CASES VALUES (10,4,'A','R');

INSERT INTO CASES VALUES (10,5,'G','V');

INSERT INTO CASES VALUES (10,6,'A','J');
INSERT INTO CASES VALUES (10,7,'A','B');
INSERT INTO CASES VALUES (10,8,'A','R');
INSERT INTO CASES VALUES (10,9,'A','V');
INSERT INTO CASES VALUES (10,10,'R','R');
COMMIT; 
-------- VÉRIFICATIONS ----------------------
SELECT * FROM CASES WHERE TYPE = 'G';
SELECT * FROM CASES WHERE TYPE ='R';
SELECT COUNT(CodeCategorie) FROM CASES GROUP BY CodeCategorie HAVING CodeCategorie ='R';
SELECT COUNT(CodeCategorie) FROM CASES GROUP BY CodeCategorie HAVING CodeCategorie ='V';
SELECT COUNT(CodeCategorie) FROM CASES GROUP BY CodeCategorie HAVING CodeCategorie ='B';
SELECT COUNT(CodeCategorie) FROM CASES GROUP BY CodeCategorie HAVING CodeCategorie ='J';